﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyOA.Model;
using System.Data.SqlClient;
using System.Data;


namespace MyOA.DAL
{
    public class SaveMyScheduleService
    {
        DBHelper db = new DBHelper();
        /// <summary>
        /// 根据机构ID来获取机构信息(给获取部门信息方法调用)
        /// </summary>
        /// <param name="branchId">机构ID</param>
        /// <returns>机构信息</returns>
        private BranchInfo getBranchInfoByBranchId(int branchId)
        {
            BranchInfo branchInfo = new BranchInfo();
            DataTable dt = db.getDataTable("sp_getBranchInfoBybranchId", new SqlParameter("@branchId", branchId));
            foreach (DataRow row in dt.Rows)
            {
                branchInfo.BranchName = (string)row[0];
                branchInfo.BranchShortName = (string)row[1];
            }

            return branchInfo;
        }

        /// <summary>
        /// 根据部门ID获取部门信息(给获取预约人集合方法调用)
        /// </summary>
        /// <param name="departId">部门ID</param>
        /// <returns>部门信息</returns>
        private DepartInfo getDepartInfoByDepartId(int departId)
        {
            DepartInfo departInfo = new DepartInfo();
            using (DataTable dt = db.getDataTable("sp_getDepartInfoByDepartId", new SqlParameter("@departId", departId)))
            {
                foreach (DataRow row in dt.Rows)
                {
                    departInfo.DepartName = (string)row[0];
                    int branchId = (int)row[1];
                    departInfo.Branch.BranchId = branchId;
                    //根据机构ID来获取机构信息
                    BranchInfo branchInfo = getBranchInfoByBranchId(branchId);
                    departInfo.Branch.BranchName = branchInfo.BranchName;
                    departInfo.Branch.BranchShortName = branchInfo.BranchShortName;
                }
            }

            return departInfo;
        }



        /// <summary>
        /// 获得所有会议类型集合
        /// </summary>
        /// <returns>会议类型集合</returns>
        public IList<MeetingInfo> getAllMeetings()
        {
            IList<MeetingInfo> meetings = new List<MeetingInfo>();
            using (SqlDataReader reader = db.getDataReader("sp_getAllMeetings"))
            {
                while (reader.Read())
                {
                    MeetingInfo meeting = new MeetingInfo();
                    meeting.MeetingId = (int)reader[0];
                    meeting.MeetingName = (string)reader[1];

                    meetings.Add(meeting);
                }
            }

            db.CloseConnection();
            return meetings;
        }

        /// <summary>
        /// 根据个人日程ID来获取这日程的预约人信息的集合
        /// </summary>
        /// <param name="scheduleId">个人日程ID</param>
        /// <returns>预约人信息集合</returns>
        public IList<UserInfo> getUserInfosByScheduleId(int scheduleId)
        {
            IList<UserInfo> userInfos = new List<UserInfo>();

            DataTable dt = db.getDataTable("sp_getUserInfosByScheduleId", new SqlParameter("@scheduleId", scheduleId));

            foreach (DataRow row in dt.Rows)
            {

                UserInfo userInfo = new UserInfo();
                userInfo.LoginId = (string)row[0];
                int departId = (int)row[1];
                userInfo.Depart.DepartId = departId;

                //调用内部方法获取部门名
                userInfo.Depart = getDepartInfoByDepartId(departId);

                userInfos.Add(userInfo);
            }



            return userInfos;
        }

        /// <summary>
        /// 新增预约人表信息
        /// </summary>
        /// <param name="preContract">预约人表信息</param>
        /// <returns>是否新增成功</returns>
        public bool insertPreContract(PreContract preContract)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@scheduleId",preContract.Schedule.ScheduleId),
                new SqlParameter("@userId",preContract.User.UserId)
            };

            if (db.getCount("sp_insertPreContract", values) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 新增个人日程
        /// </summary>
        /// <param name="schedule">要新增的个人日程的数据</param>
        /// <returns>是否新增成功</returns>
        public bool insertSchedule(Schedule schedule)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
               
            };


            int count = db.getCount("sp_insertSchedule", values);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据登陆号来获取登陆用户信息
        /// </summary>
        /// <param name="loginId">登陆号</param>
        /// <returns>登陆用户信息</returns>
        public UserInfo getUserInfoByLoginId(string loginId)
        {
            UserInfo userInfo = new UserInfo();
            using (DataTable dt = db.getDataTable("sp_getUserInfoByLoginId", new SqlParameter("@loginId", loginId)))
            {
                foreach (DataRow row in dt.Rows)
                {
                    userInfo.UserName = (string)row[0];
                    userInfo.Gender = (int)row[1];
                    userInfo.Role.RoleId = (int)row[2];
                    userInfo.Depart.DepartId = (int)row[3];
                    userInfo.UserState.UserStateId = (int)row[4];
                    userInfo.UserId = (int)row[5];

                }
            }
            return userInfo;

        }

        /// <summary>
        /// 修改个人日程信息
        /// </summary>
        /// <param name="schedule">个人日程ID</param>
        /// <returns>是否修改成功</returns>
        public bool updateScheduleByScheduleId(Schedule schedule)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
                new SqlParameter("@scheduleId",schedule.ScheduleId)
               
            };

            if (db.getCount("sp_updateScheduleByScheduleId", values) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取全部的机构信息
        /// </summary>
        /// <returns>获取全部的机构信息</returns>
        public IList<BranchInfo> getAllBranchInfo()
        {
            IList<BranchInfo> branches = new List<BranchInfo>();
            using (DataTable dt = db.getDataTable("sp_getAllBranchInfo"))
            {
                foreach (DataRow row in dt.Rows)
                {
                    BranchInfo branch = new BranchInfo();
                    branch.BranchId = (int)row[0];
                    branch.BranchName = (string)row[1];
                    branch.BranchShortName = (string)row[2];

                    branches.Add(branch);
                }
            }

            return branches;
        }

        /// <summary>
        /// 获取全部的部门信息
        /// </summary>
        /// <returns>部门信息集合</returns>
        public IList<DepartInfo> getAllDepartInfo()
        {

            IList<DepartInfo> departs = new List<DepartInfo>();
            using (DataTable dt = db.getDataTable("sp_getAllDepartInfo"))
            {
                foreach (DataRow row in dt.Rows)
                {
                    DepartInfo depart = new DepartInfo();
                    depart.DepartId = (int)row[0];
                    depart.DepartName = (string)row[1];
                    int branchId = (int)row[2];
                    depart.Branch.BranchId = branchId;



                    departs.Add(depart);
                }
            }
            return departs;
        }

        /// <summary>
        /// 获取全部的用户信息
        /// </summary>
        /// <returns>全部的用户信息</returns>
        public IList<UserInfo> getAllUserInfo()
        {

            IList<UserInfo> users = new List<UserInfo>();
            DataTable dt = db.getDataTable("sp_getAllUserInfo");
            foreach (DataRow row in dt.Rows)
            {
                UserInfo user = new UserInfo();
                user.UserId = (int)row[0];
                user.LoginId = (string)row[1];
                user.UserName = (string)row[2];
                int departId = (int)row[3];

                user.Depart.DepartId = departId;
                users.Add(user);
            }
            return users;
        }

        /// <summary>
        /// 根据预约人ID和日程信息 来增加个人日程和预约信息
        /// </summary>
        /// <param name="schedule">个人日程信息</param>
        /// <param name="usersId">预约人组成的字符串</param>
        /// <returns>是否成功</returns>
        public int insertScheduleAndPreContract(Schedule schedule, string usersId)
        {

            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
                new SqlParameter("@usersId",usersId)

            };
            int count = 0;
            count = db.getCount("sp_insertScheduleAndPreContract", values);
            return count;

        }

        /// <summary>
        /// 根据个人日程ID查找预约人信息集合
        /// </summary>
        /// <param name="scheduleId">个人日程ID</param>
        /// <returns>预约人信息集合</returns>
        public IList<PreContract> getPreContractByScheduleId(int scheduleId)
        {
            IList<PreContract> preContracts = new List<PreContract>();

            using (SqlDataReader reader = db.getDataReader("sp_getPreContractByScheduleId", new SqlParameter("scheduleId", scheduleId)))
            {
                while (reader.Read())
                {
                    PreContract preContract = new PreContract();
                    preContract.PreContractId = (int)reader[0];
                    preContract.User.UserId = (int)reader[1];
                    preContracts.Add(preContract);
                }
            }
            db.CloseConnection();
            return preContracts;
        }

        /// <summary>
        /// 根据个人日程ID 来删除带有预约人信息（同时删除2张表的数据）
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        public bool DeleteScheduleAndPreContractByScheduleId(int scheduleId)
        {
            if (db.getCount("sp_DeleteScheduleAndPreContractByScheduleId", new SqlParameter("@scheduleId", scheduleId)) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据个人日程ID 来删除带有预约人信息（删除1张表的数据）
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        public bool DeleteScheduleByScheduleId(int scheduleId)
        {
            if (db.getCount("sp_DeleteScheduleByScheduleId", new SqlParameter("@scheduleId", scheduleId)) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改个人日程并增加预约人信息
        /// </summary>
        /// <param name="schedule">个人日程信息</param>
        /// <param name="usersId">增加预约人的条件字符串 即USERID</param>
        /// <returns>影响的行数</returns>
        public int updateScheduleAndInsertPreContract(Schedule schedule, string usersId)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@scheduleId",schedule.ScheduleId),
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
                new SqlParameter("@usersId",usersId)

            };
            int count = 0;
            count = db.getCount("sp_updateScheduleAndInsertPreContract", values);
            return count;
        }

        /// <summary>
        /// 修改个人日程并删除预约人信息
        /// </summary>
        /// <param name="schedule">个人日程信息</param>
        /// <param name="usersId">删除预约人的条件字符串 即USERID</param>
        /// <returns>影响的行数</returns>
        public int updateScheduleAndDeletePreContract(Schedule schedule, string usersId)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@scheduleId",schedule.ScheduleId),
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
                new SqlParameter("@usersId",usersId)

            };
            int count = 0;
            count = db.getCount("sp_updateScheduleAndDeletePreContract", values);
            return count;
        }

        /// <summary>
        /// 修改个人日程并增加预约人和删除预约人
        /// </summary>
        /// <param name="schedule">要修改的个人日程信息</param>
        /// <param name="insertUserId">增加预约人的USERID字符串</param>
        /// <param name="deleteUserId">删除预约人的USERID字符串</param>
        /// <returns></returns>
        public int updateScheduleAndInsertAndDeletePreContract(Schedule schedule, string insertUserId, string deleteUserId)
        {
            SqlParameter[] values = new SqlParameter[]
            {
                new SqlParameter("@scheduleId",schedule.ScheduleId),
                new SqlParameter("@title",schedule.Title),
                new SqlParameter("@address",schedule.Address),
                new SqlParameter("@beginTime",schedule.BeginTime),
                new SqlParameter("@endTime",schedule.EndTime),
                new SqlParameter("@createTime",schedule.CreateTime),
                new SqlParameter("@schContent",schedule.SchContent),
                new SqlParameter("@meetingId",schedule.Meeting.MeetingId),
                new SqlParameter("@createUserId",schedule.CreateUser.UserId),
                new SqlParameter("@createDepartId",schedule.CreateDepart.DepartId),
                new SqlParameter("@insertUserId",insertUserId),
                new SqlParameter("@deleteUserId",deleteUserId)

            };
            int count = 0;
            count = db.getCount("sp_updateScheduleAndInsertAndDeletePreContract", values);
            return count;
        }

        /// <summary>
        /// 根据日程ID获取个人日程信息
        /// </summary>
        /// <param name="scheduleId">个人日程ID</param>
        /// <returns>个人日程信息</returns>
        public Schedule getScheduleByScheduleId(int scheduleId)
        {
            Schedule schedule = new Schedule();
            using (SqlDataReader reader = db.getDataReader("sp_getScheduleByScheduleId", new SqlParameter("@scheduleId", scheduleId)))
            {
                if (reader.Read())
                {
                    schedule.ScheduleId = (int)reader[0];
                    schedule.Title = (string)reader[1];
                    schedule.Address = (string)reader[2];
                    schedule.BeginTime = (DateTime)reader[3];
                    schedule.EndTime = (DateTime)reader[4];
                    schedule.CreateTime = (DateTime)reader[5];
                    schedule.SchContent = (string)reader[6];
                    schedule.Meeting.MeetingId = (int)reader[7];
                    schedule.CreateUser.UserId = (int)reader[8];
                    schedule.CreateDepart.DepartId = (int)reader[9];
                }
            }
            db.CloseConnection();
            schedule.CreateUser.LoginId = getLoginIdByUserId(schedule.CreateUser.UserId);
            return schedule;
        }

        /// <summary>
        /// 根据登陆ID来获取登陆名
        /// </summary>
        /// <param name="userId">登陆ID</param>
        /// <returns>返回登陆名</returns>
        private string getLoginIdByUserId(int userId)
        {
            return (string)db.getScalar("sp_getLoginIdByUserId", new SqlParameter("@userId", userId));
        }

    }
}
